"""
在线词典 ： 服务端数据处理
"""
import pymysql


class DictData:
    def __init__(self):
        self.kwargs = {
            "user": "root",
            "password": "123456",
            "database": "dict",
            "charset": "utf8"
        }
        self.db = pymysql.connect(**self.kwargs)
        self.cur = self.db.cursor()

    def close(self):
        self.cur.close()
        self.db.close()

    def sign(self, name, passwd):
        sql = "insert into user (user,password) values(%s,%s);"
        try:
            self.cur.execute(sql,[name, passwd])
            self.db.commit()
            return True
        except Exception as e:
            print(e)
            self.db.rollback()

    def login(self,name,passwd):
        sql="select id from user where binary user=%s and password=%s;"
        self.cur.execute(sql,[name,passwd])
        return self.cur.fetchone()

    def query(self,word):
        sql="select id,mean from words where word=%s;"
        self.cur.execute(sql,[word])
        return self.cur.fetchone() #(id,mean) None

    def insert_hist(self,user_id,words_id):
        sql="insert into hist (user_id,words_id) values (%s,%s);"
        try:
            self.cur.execute(sql,[user_id,words_id])
            self.db.commit()
        except:
            self.db.rollback()

    def hist(self,user_id):
        sql="select user,word,time " \
            "from hist inner join user " \
            "on hist.user_id=user.id " \
            "inner join words " \
            "on hist.words_id=words.id " \
            "where user_id=%s " \
            "order by time desc " \
            "limit 10;"
        self.cur.execute(sql,[user_id])
        return self.cur.fetchall()


